import pymysql


def get_num_periods(lottery_type,state):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='lottery',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    num_periods = ""
    try:
        with db.cursor() as cursor:

            sql = "select distinct num_periods from lottery.lottery_state a where a.state = "+ str(state) +" and lottery_type = '" + lottery_type + "';"
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            # print(len(result))
            for row in result:
                # print(row)
                num_periods = row["num_periods"]
    finally:
        db.close()  # 关闭数据库连接

    return num_periods


def get_new_num_periods(lottery_type,state):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='lottery',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    return_result = []
    try:
        with db.cursor() as cursor:

            sql = "select min(record_date) as record_date,min(num_periods) as num_periods from lottery.lottery_state a where a.state = "+ str(state) +" and lottery_type = '" + lottery_type + "';"
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            print(sql)
            # print(len(result))
            for row in result:
                print(row['record_date']+"-----"+row['num_periods'])
                return_result.append(row['record_date'])
                return_result.append(row['num_periods'])
    finally:
        db.close()  # 关闭数据库连接

    return return_result


def get_lottery_num_mysql(table_name,num_periods):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='lottery',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    num = []
    try:
        with db.cursor() as cursor:

            sql = "select distinct num1,num2,num3,num4,num5,num6,num7 from lottery." + table_name + " a where a.num_periods = '"+ str(num_periods) + "';"
            # print(sql)
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            # print(len(result))
            i = 0
            for row in result:
                # print(row)

                num2 = []
                for key in row.keys():
                    num2.append(row[key])
                num.append(num2)
                i = i + 1
                # print(num)
    finally:
        db.close()  # 关闭数据库连接

    return num


def save_mysql_lottery (sql,values):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='lottery',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    lotteryDrawResult = []
    try:
        with db.cursor() as cursor:
            cursor.execute(sql, values)
            db.commit()  # 提交事务
    finally:
        db.close()  # 关闭数据库连接

    return 0
